using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
using MOD;

namespace DOM
{
    public  class StudentDOM
    {
        public bool InsertStudent(string studentName, string studentId, string acadamy, string sex, DateTime birthday, string transcriptId, string courseId)
        {
            OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data\selectCourse.mdb");
            try
            {
                con.Open();
                OleDbCommand cmd = con.CreateCommand();
                cmd.CommandText = "INSERT INTO student (studentName,studentId,acadamy,sex,birthday,transcriptId,courseId) VALUES (@studentName,@studentId,@acadamy,@sex,@birthday,@transcriptId,@courseId)";
                cmd.Parameters.AddWithValue("@studentName", studentName);
                cmd.Parameters.AddWithValue("@studentId", studentId);
                cmd.Parameters.AddWithValue("@acadamy", acadamy);
                cmd.Parameters.AddWithValue("@sex", sex);
                cmd.Parameters.AddWithValue("@birthday", birthday);
                cmd.Parameters.AddWithValue("@transcriptId", transcriptId);
                cmd.Parameters.AddWithValue("@courseId", courseId);
                cmd.ExecuteNonQuery();
                con.Close();
                return true;
            }
            catch (Exception)
            {
               // MessageBox.Show(" database error!" + e.Message);
                return false;

            }
        }


        public bool UpdateStudent(string studentName, string studentId, string acadamy, string sex, DateTime birthday, string transcriptId, string courseId)
        {
            OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data\selectCourse.mdb");
            try
            {
                con.Open();
                string queryStr = "UPDATE student SET studentName=@studentName,studentId=@studentId, acadamy =@acadamy,sex=@sex,birthday=@birthday,transcriptId=@transcriptId,courseId=@courseId";
                OleDbCommand cmd = new OleDbCommand();
                cmd.CommandText = queryStr;
                cmd.Parameters.AddWithValue("@studentName", studentName);
                cmd.Parameters.AddWithValue("@studentId", studentId);
                cmd.Parameters.AddWithValue("@acadamy", acadamy);
                cmd.Parameters.AddWithValue("@sex", sex);
                cmd.Parameters.AddWithValue("@birthday", birthday);
                cmd.Parameters.AddWithValue("@transcriptId",transcriptId);
                cmd.Parameters.AddWithValue("@courseId", courseId);
                cmd.ExecuteNonQuery();
                con.Close();
                return true;

            }
            catch
            {
                return false;
            }
        }

        public DataTable SelectById(string stuid)
        {
            OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data\selectCourse.mdb");
            con.Open();
            string queryStr = "SELECT * FROM [student] WHERE studentId = @stuid";
            OleDbDataAdapter result = new OleDbDataAdapter(queryStr, con);
            result.SelectCommand.Parameters.AddWithValue("@stuid", stuid);
            DataTable dt = new DataTable();
            result.Fill(dt);
            con.Close();
            return dt;
        }
        public DataTable SelectAll()
        {
            OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data\selectCourse.mdb");
            con.Open();
            string queryString = "SELECT * FROM [student]";
            OleDbDataAdapter result = new OleDbDataAdapter(queryString, con);
            DataTable dt = new DataTable();
            result.Fill(dt);
            return dt;
        }

    }
}
